package music.dao;

import music.model.CommentVO;
import music.utils.dbcp.OracleDBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class CommentDAO {

    private static String writeComment = "insert into comments(cno, comments, userid, ref, cref) values(cno_seq.nextval,? , ? , ?,cno_seq.currval)";
    private static String writeReply = "insert into comments(cno, comments, userid, ref, cref) values(cno_seq.nextval,? , ? , ?, ?)";
    private static String selectComment = "select * from comments where ref = ? order by cref,cno asc";


    private static String updateComment = "update comments set bdtitle = ?, contents = ?, regdate=sysdate where bdno = ?";
    private static String deleteComment = "delete from comments where cno=?";

    // cno comments userid regdate ref cref


    public static boolean writeComment(CommentVO cvo) {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean isWrite = false;

        try {
            conn = OracleDBCP.openDBCP();
            ps = conn.prepareStatement(writeComment);
            ps.setString(1, cvo.getComments());
            ps.setString(2, cvo.getUserid());
            ps.setString(3, cvo.getRef());

            if (ps.executeUpdate() > 0) isWrite = true;

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            OracleDBCP.closeDBCP(conn, ps, null);
        }

        return isWrite;
    }

    public static boolean writeReply(CommentVO cvo) {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean isWrite = false;

        System.out.println(cvo.getComments());

        try {
            conn = OracleDBCP.openDBCP();
            ps = conn.prepareStatement(writeReply);
            ps.setString(1, cvo.getComments());
            ps.setString(2, cvo.getUserid());
            ps.setString(3, cvo.getRef());
            ps.setString(4, cvo.getCref());

            if (ps.executeUpdate() > 0) isWrite = true;

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            OracleDBCP.closeDBCP(conn, ps, null);
        }

        return isWrite;
    }


    /*
    public static boolean updateComment(CommentVO cvo) {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean isUpdate = false;

        try {
            conn = OracleDBCP.openDBCP();
            ps = conn.prepareStatement(updateComment);
            ps.setString(1, cvo.getBdtitle());
            ps.setString(2, cvo.getContents());
            ps.setString(3, cvo.getBdno());

            if (ps.executeUpdate() > 0)
                isUpdate = true;

        } catch (Exception ex) {
            System.out.println("updateBoard에서 오류 발생");
            ex.printStackTrace();

        } finally {
            OracleDBCP.closeDBCP(conn, ps, null);
        }
        return isUpdate;
    }


    public static boolean deleteComment(String cno) {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean isDelete = false;

        try {
            conn = OracleDBCP.openDBCP();
            ps = conn.prepareStatement(deleteComment);
            ps.setString(1, cno);

            if (ps.executeUpdate() > 0) isDelete = true;

        } catch (Exception ex) {
            System.out.println("deleteBoard에서 오류 발생!!");
            ex.printStackTrace();
        } finally {
            OracleDBCP.closeDBCP(conn, ps, null);
        }
        return isDelete;
    }*/

/*

    public static String selectComment(String bdno) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String result = "";

        try {
            conn = OracleDBCP.openDBCP();
            ps = conn.prepareStatement(selectComment);
            ps.setString(1, bdno);
            rs = ps.executeQuery();

            while (rs.next()) {
                result += rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3)
                        + " " + rs.getString(4) + " " + rs.getString(5) + " " + rs.getString(6)
                        + " " + rs.getString(8) + "/";

            */

            /*result += "<tr>";
                if(rs.getString(1)==rs.getString(6)){
                    // 댓글
                    if( Integer.parseInt(rs.getString(7)) > 1) {
                        result += "<td class='cmhd' rowspan='"+rs.getString(7)+ "'>";
                    }else{
                        result += "<td>";
                    }
                    result += rs.getString(3) + "</td><td>" + rs.getString(2) +"</td>";
                }else{
                    // 대댓글
                    result += "<td><p class='rpdate'>" + rs.getString(3) + "</p><p>"
                            + rs.getString(2) + "</p>";

                }

                result += "</td><td class='cmdright'>"
                        + rs.getString(4).split(" ")[0] + "<br/>" + rs.getString(4).split(" ")[1] +"<br/>"
                        + "<a href='#' class='btn'>[덧글]</a>${loginComment}</td></tr>";
                */

            /*
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            OracleDBCP.closeDBCP(conn, ps, rs);
        }

        return result;

    }
*/


    public static ArrayList<CommentVO> selectComment(String ref) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<CommentVO> listC = new ArrayList<>();

        try {
            conn = OracleDBCP.openDBCP();


            ps = conn.prepareStatement(selectComment);
            ps.setString(1, ref);
            rs = ps.executeQuery();

            while (rs.next()) {
                CommentVO cvo = new CommentVO();
                cvo.setCno(rs.getString(1));
                cvo.setComments(rs.getString(2));
                cvo.setUserid(rs.getString(3));
                cvo.setRegdate(rs.getString(4));
                cvo.setRef(rs.getString(5));
                cvo.setCref(rs.getString(6));

                listC.add(cvo);
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            OracleDBCP.closeDBCP(conn, ps, rs);
        }

        return listC;

    }

}
